<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Linksrecord extends Model
{
    protected $table = 'links_record_';
    protected $primaryKey = 'id';

    public function __construct(array $custb=[])
    {
        if (empty($custb)) {
            $this->table = 'links_record_'.(date("m",time())%4);
        } else {
            $this->table = 'links_record_'.$custb["tbnum"];
        }
    }

    public function creatLinkRecordWeb($record_id,$links_id,$province,$city,$system,$device,$browser,$ip,$code,$web_time)
    {
        return self::insert(["record_id"=>$record_id,"links_id"=>$links_id,"province"=>$province,"city"=>$city,"system"=>$system,"device"=>$device,"browser"=>$browser,"ip"=>$ip,"code"=>$code,"web_time"=>$web_time,"created_at"=>$web_time]);
    }

    public function updateLinkRecordWeb($record_id,$mini_open_time,$mini_show_time,$mini_press_time,$mini_card_time,$mini_hide_time,$qrcodes_id)
    {
        return self::where("record_id",$record_id)->update(["mini_open_time"=>$mini_open_time,"mini_show_time"=>$mini_show_time,"mini_press_time"=>$mini_press_time,"mini_card_time"=>$mini_card_time,"mini_hide_time"=>$mini_hide_time,"qrcodes_id"=>$qrcodes_id]);
    }

    public function creatLinkRecordMini($record_id,$links_id,$province,$city,$system,$device,$browser,$ip,$code,$mini_open_time,$mini_show_time,$mini_press_time,$mini_card_time,$mini_hide_time)
    {
        return self::insert(["record_id"=>$record_id,"links_id"=>$links_id,"province"=>$province,"city"=>$city,"system"=>$system,"device"=>$device,"browser"=>$browser,"ip"=>$ip,"code"=>$code,"mini_open_time"=>$mini_open_time,"mini_show_time"=>$mini_show_time,"mini_press_time"=>$mini_press_time,"mini_card_time"=>$mini_card_time,"mini_hide_time"=>$mini_hide_time,"created_at"=>$mini_open_time]);
    }

    public function creatLinkRecordBatch($linksRecords)
    {
        return self::insert($linksRecords);
    }

    public function updateLinkRecordPressTime($record_id,$mini_press_time)
    {
        return self::where("record_id",$record_id)->update(["mini_press_time"=>$mini_press_time]);
    }

    public function updateLinkRecordShowTime($record_id,$mini_show_time,$mini_open_time)
    {
        return self::where("record_id",$record_id)->update(["mini_show_time"=>$mini_show_time,"mini_open_time"=>$mini_open_time]);
    }

    public function linkRecordLists($links_id,$mini_show_time,$mini_press_time,$mini_card_time,$date_start,$date_end,$code,$province)
    {
        $query = self::where('links_id', $links_id);
        if ($mini_show_time == 1) $query->where("mini_show_time",">",1);
        if ($mini_show_time == 2) $query->where("mini_show_time","=",1);
        if ($mini_press_time == 1) $query->where("mini_press_time",">",1);
        if ($mini_press_time == 2) $query->where("mini_press_time","=",1);
        if ($mini_card_time == 1) $query->where("mini_card_time",">",1);
        if ($mini_card_time == 2) $query->where("mini_card_time","=",1);
        if (!empty($code)) $query->where("code",$code);
        if (!empty($province)) $query->where("province",$province);
        if (!empty($date_start)) $query->whereBetween('created_at',[$date_start,$date_end]);
        return $query->orderBy("id","DESC")->paginate(25);
    }

    public function linkRecordListSample($links_id,$mini_show_time,$mini_press_time,$mini_card_time,$date_start,$date_end,$offset,$limit)
    {
        $query = self::where('links_id', $links_id);
        if ($mini_show_time == 1) $query->where("mini_show_time",">",1);
        if ($mini_show_time == 2) $query->where("mini_show_time","=",1);
        if ($mini_press_time == 1) $query->where("mini_press_time",">",1);
        if ($mini_press_time == 2) $query->where("mini_press_time","=",1);
        if ($mini_card_time == 1) $query->where("mini_card_time",">",1);
        if ($mini_card_time == 2) $query->where("mini_card_time","=",1);
        if (!empty($date_start)) $query->whereBetween('created_at',[$date_start,$date_end]);
        return $query->offset($offset)->limit($limit)->orderBy("id","DESC")->get();
    }

    public function linkRecordListSampleCount($links_id,$mini_show_time,$mini_press_time,$mini_card_time,$date_start,$date_end)
    {
        $query = self::where('links_id', $links_id);
        if ($mini_show_time == 1) $query->where("mini_show_time",">",1);
        if ($mini_show_time == 2) $query->where("mini_show_time","=",1);
        if ($mini_press_time == 1) $query->where("mini_press_time",">",1);
        if ($mini_press_time == 2) $query->where("mini_press_time","=",1);
        if ($mini_card_time == 1) $query->where("mini_card_time",">",1);
        if ($mini_card_time == 2) $query->where("mini_card_time","=",1);
        if (!empty($date_start)) $query->whereBetween('created_at',[$date_start,$date_end]);
        return $query->count("id");
    }

    public function getLinkslistsipsCount($links_id,$status,$province,$date_start,$date_end)
    {
        $query = self::where('links_id', $links_id);
        if (!empty($status)) $query->where('status', $status);
        if (!empty($province)) $query->where('province', $province);
        if (!empty($date_start)) $query->where('created_at','>=', $date_start);
        if (!empty($date_end)) $query->where('created_at','<=', $date_end);
        return $query->count("id");
    }

    public function getLinkRecordDetial($record_id)
    {
        return self::where("record_id", $record_id)->first();
    }

    public function updateLinksIpsStatus($id,$status,$active)
    {
        return self::where('id', $id)->update(["status"=>$status,"active"=>$active]);
    }

    public function getSystemDatas($links_id)
    {
        return self::select(DB::raw('count(id) as system_count, system'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('system')
                     ->get();
    }

    public function getBrowserDatas($links_id)
    {
        return self::select(DB::raw('count(id) as browser_count, browser'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('browser')
                     ->get();
    }

    public function getDeviceDatas($links_id)
    {
        return self::select(DB::raw('count(id) as device_count, device'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('device')
                     ->get();
    }

    public function getProvinceDatas($links_id)
    {
        return self::select(DB::raw('count(id) as province_count, province'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('province')
                     ->get();
    }

    public function getDailyCount($links_id,$keyword,$start_time,$end_time)
    {
        return self::where('links_id', $links_id)
                     ->where($keyword,">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDailyDirectCount($links_id,$start_time,$end_time)
    {
        return self::where('links_id', $links_id)
                     ->where("web_time",1)
                     ->where("mini_show_time",">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDefaultDatas($links_id,$start_time,$end_time,$datebatch)
    {
        if ($datebatch == "day") {
            $unit = "%Y-%m-%d";
        } else {
            $unit = "%H";
        }
        return self::select(DB::raw('count(id) as count, DATE_FORMAT(FROM_UNIXTIME(created_at),"'.$unit.'") as `date`,status'))
                     ->where('links_id', $links_id)
                     ->whereBetween('created_at', [$start_time,$end_time])
                     ->distinct('ip')
                     ->groupBy('date')
                     ->groupBy('status')
                     ->get();
    }

    public function getLinksRecordHour($links_id,$keyword,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where links_id = "'.$links_id.'" and '.$keyword.' > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function rdirectLinksRecordHour($links_id,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where links_id = "'.$links_id.'" and web_time = 1 and mini_open_time > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function getLinkExportCount($links_id,$start_time,$end_time)
    {
        return self::where('links_id',$links_id)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function updateLinkQrcodeShow($record_id,$mini_show_time,$qrcodes_id)
    {
        return self::where("record_id",$record_id)->update(["mini_show_time"=>$mini_show_time,"qrcodes_id"=>$qrcodes_id]);
    }

    public function updateLinkQrcodePress($record_id,$mini_press_time)
    {
        return self::where("record_id",$record_id)->update(["mini_press_time"=>$mini_press_time]);
    }
}
